package br.ufu.facom.pillbox.dao;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import br.ufu.facom.pillbox.domain.Agenda;
import br.ufu.facom.pillbox.domain.MedicamentoDTO;
import br.ufu.facom.sql.Sql;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class AgendaDAO {
	
	DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	DateFormat dfDateTime = new SimpleDateFormat("yyyy-MM-ddhh:mm");
	private SQLiteDatabase database;
	private String[] columns = { CustomSQLiteOpenHelper.COLUMN_ID,
			CustomSQLiteOpenHelper.COLUMN_TM,
			CustomSQLiteOpenHelper.COLUMN_MEDIC,
			CustomSQLiteOpenHelper.COLUMN_DT_INI,
			CustomSQLiteOpenHelper.COLUMN_DT_FIM,
			CustomSQLiteOpenHelper.COLUMN_DT_NOT,
			
			CustomSQLiteOpenHelper.COLUMN_TM_NOT,
			CustomSQLiteOpenHelper.COLUMN_TIPO};

	private CustomSQLiteOpenHelper sqliteOpenHelper;

	public AgendaDAO(Context context) {
		Log.d(this.getClass().getCanonicalName(), "SQLSetup");
		sqliteOpenHelper = new CustomSQLiteOpenHelper(context);
	}

	public void open() throws SQLException {
		Log.d(this.getClass().getCanonicalName(), "SQLOpen");
		database = sqliteOpenHelper.getWritableDatabase();
	}

	public void close() {
		sqliteOpenHelper.close();
	}

	public void commit() {
		close();
		open();
	}

	public void create(Agenda agenda) {
		Log.d(this.getClass().getCanonicalName(), "SQLcreate");

		ContentValues values = new ContentValues();
		values.put(CustomSQLiteOpenHelper.COLUMN_TM, agenda.getTM());
		values.put(CustomSQLiteOpenHelper.COLUMN_MEDIC, agenda.getMedic());
		values.put(CustomSQLiteOpenHelper.COLUMN_DT_INI, agenda.getDTIni());
		values.put(CustomSQLiteOpenHelper.COLUMN_DT_FIM, agenda.getDTFim());
		values.put(CustomSQLiteOpenHelper.COLUMN_DT_NOT, agenda.getDTNot());
		values.put(CustomSQLiteOpenHelper.COLUMN_TM_NOT, agenda.getTMNot());
		values.put(CustomSQLiteOpenHelper.COLUMN_TIPO, agenda.getTipo());

		if (agenda.getId() != null) {

			long updateId = database.update(
					CustomSQLiteOpenHelper.TABLE_AGENDA, values,
					CustomSQLiteOpenHelper.COLUMN_ID + " = " + agenda.getId(),
					null);

			Log.d(this.getClass().getCanonicalName(),
					"update " + String.valueOf(updateId));
		}

		else {
			long insertId = database.insert(
					CustomSQLiteOpenHelper.TABLE_AGENDA, null, values);
			agenda.setId(insertId); // inserido Carlos Camacho 8/4
			Log.d(this.getClass().getCanonicalName(),
					"insert " + String.valueOf(insertId));
		}

		commit();
	}

	public void delete(Agenda agenda) {
		Log.d(this.getClass().getCanonicalName(), "SQLdelete");

		if (agenda.getId() != null) {
			database.delete(CustomSQLiteOpenHelper.TABLE_AGENDA,
					CustomSQLiteOpenHelper.COLUMN_ID + " = " + agenda.getId(),
					null);
			commit();
		}
	}

	public Agenda getByKey(Integer id) {
		Log.d(this.getClass().getCanonicalName(), "SQLGet " + id);
		Log.d(this.getClass().getCanonicalName(), "cursor");

		Cursor cursor = database.query(CustomSQLiteOpenHelper.TABLE_AGENDA,
				columns, CustomSQLiteOpenHelper.COLUMN_ID + " = " + id, null,
				null, null, null);

		Agenda agenda = new Agenda();

		if (cursor.getCount() > 0) {
			Log.d(this.getClass().getCanonicalName(), "top");

			cursor.moveToFirst();
			agenda.setId(cursor.getLong(0));
			agenda.setTM(cursor.getString(1));
			agenda.setMedic(cursor.getString(2));
			agenda.setDTIni(cursor.getString(3));
			agenda.setDTFim(cursor.getString(4));
			agenda.setDTNot(cursor.getString(5));
			agenda.setTMNot(cursor.getString(6));

			Log.d(this.getClass().getCanonicalName(), "fromDisk");
		}

		Log.d(this.getClass().getCanonicalName(), "closeCursor");
		cursor.close();

		return agenda;
	}
	
	public List<Agenda> getByName(String medic) {

		Cursor cursor = database.rawQuery("" +
				"select " +
					CustomSQLiteOpenHelper.COLUMN_ID + ", " + 
					CustomSQLiteOpenHelper.COLUMN_TM + ", " + 
					CustomSQLiteOpenHelper.COLUMN_MEDIC + ", " + 
					CustomSQLiteOpenHelper.COLUMN_DT_INI + ", " + 
					CustomSQLiteOpenHelper.COLUMN_DT_FIM + ", " + 
					CustomSQLiteOpenHelper.COLUMN_DT_NOT + ", " + 
					CustomSQLiteOpenHelper.COLUMN_TM_NOT + ", " + 
					CustomSQLiteOpenHelper.COLUMN_TIPO  + 
				" from agenda where medic like '" + medic +"%'", null);

		Agenda agenda = new Agenda();

		ArrayList<Agenda> agendas = new ArrayList<Agenda>();
        
        if (cursor.moveToFirst()) {
            do {
            	agenda.setId(cursor.getLong(0));
    			agenda.setTM(cursor.getString(1));
    			agenda.setMedic(cursor.getString(2));
    			agenda.setDTIni(cursor.getString(3));
    			agenda.setDTFim(cursor.getString(4));
    			agenda.setDTNot(cursor.getString(5));
    			agenda.setTMNot(cursor.getString(6));
    			agenda.setTipo(cursor.getString(7));
    			
    			agendas.add(agenda);
            } while (cursor.moveToNext());
         }
         if (cursor != null && !cursor.isClosed()) {
            cursor.close();
         }
        return agendas;

	}

	public List<Agenda> getPeriodo(String tm1, String tm2) {
		Log.d(this.getClass().getCanonicalName(), "SQLLoad");
		List<Agenda> agendas = new ArrayList<Agenda>();
		String where;
		if (tm1 == null || tm1.length() == 0)
			if (tm2 == null || tm2.length() == 0)
				where = null;
			else
				where = CustomSQLiteOpenHelper.COLUMN_TM + " <= '" + tm2 + "'";

		else if (tm2 == null || tm2.length() == 0)
			where = CustomSQLiteOpenHelper.COLUMN_TM + " >= '" + tm1 + "'";

		else
			where = CustomSQLiteOpenHelper.COLUMN_TM + " between '" + tm1
					+ "' and '" + tm2 + "'";

		Log.d(this.getClass().getCanonicalName(), "cursor");

		Cursor cursor = database.query(CustomSQLiteOpenHelper.TABLE_AGENDA,
				columns, where, null, null, null, null);

		Log.d(this.getClass().getCanonicalName(), "top");
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			Agenda agenda = new Agenda();
			agenda.setId(cursor.getLong(0));
			agenda.setTM(cursor.getString(1));
			agenda.setMedic(cursor.getString(2));
			agenda.setDTIni(cursor.getString(3));
			agenda.setDTFim(cursor.getString(4));
			agenda.setDTNot(cursor.getString(5));
			agenda.setTMNot(cursor.getString(6));
			// agenda.setOnDisk(true);
			agendas.add(agenda);
			cursor.moveToNext();
		}
		Log.d(this.getClass().getCanonicalName(), "closeCursor");
		cursor.close();
		return agendas;
	}

	public List<Agenda> getRecentes(String dt, String tm) {
		Log.d(this.getClass().getCanonicalName(), "SQLRecent " + dt + " " + tm);
		List<Agenda> agendas = new ArrayList<Agenda>();
		String where;
		where = Sql.b2in_x_over(Sql.tm2time(CustomSQLiteOpenHelper.COLUMN_TM),
				Sql.tm2time(Sql.lit(tm), "'-15 minutes'"),
				Sql.tm2time(Sql.lit(tm)));
		where += Sql.iop("",
				new String[] { " and (", CustomSQLiteOpenHelper.COLUMN_DT_INI,
						" = '' or ", CustomSQLiteOpenHelper.COLUMN_DT_INI,
						" <= ", Sql.dt2date(Sql.lit(dt)), ")" });
		where += Sql.iop("",
				new String[] { " and (", CustomSQLiteOpenHelper.COLUMN_DT_FIM,
						" = '' or ", CustomSQLiteOpenHelper.COLUMN_DT_FIM,
						">=", Sql.dt2date(Sql.lit(dt)), ")" });
		where += Sql.iop(
				"",
				new String[] { " and (", CustomSQLiteOpenHelper.COLUMN_DT_NOT,
						" = '' or ", CustomSQLiteOpenHelper.COLUMN_DT_NOT,
						" < ", Sql.dt2date(Sql.lit(dt)), " or ",
						CustomSQLiteOpenHelper.COLUMN_TM_NOT, " < ",
						Sql.tm2time(Sql.lit(tm), "'-15 minutes'"), ")" });

		Log.d(this.getClass().getCanonicalName(), "cursor for " + where);

		Cursor cursor = database.query(CustomSQLiteOpenHelper.TABLE_AGENDA,
				columns, where, null, null, null, null);

		Log.d(this.getClass().getCanonicalName(), "top " + cursor.getCount());
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			Agenda agenda = new Agenda();
			agenda.setId(cursor.getLong(0));
			agenda.setTM(cursor.getString(1));
			agenda.setMedic(cursor.getString(2));
			agenda.setDTIni(cursor.getString(3));
			agenda.setDTFim(cursor.getString(4));
			agenda.setDTNot(cursor.getString(5));
			agenda.setTMNot(cursor.getString(6));
			// agenda.setOnDisk(true);
			agendas.add(agenda);
			cursor.moveToNext();
		}
		Log.d(this.getClass().getCanonicalName(), "closeCursor");
		cursor.close();
		return agendas;
	}
	
	public List<MedicamentoDTO> buscarProximasMedicacoes() {
		
		ArrayList<MedicamentoDTO> retorno = new ArrayList<MedicamentoDTO>();
		
		try {
			String data = df.format(new Date());
			Cursor cursor = database.rawQuery("" +
					"select " +
						CustomSQLiteOpenHelper.COLUMN_ID + ", " + 
						CustomSQLiteOpenHelper.COLUMN_TM + ", " + 
						CustomSQLiteOpenHelper.COLUMN_MEDIC + ", " + 
						CustomSQLiteOpenHelper.COLUMN_DT_INI + ", " + 
						CustomSQLiteOpenHelper.COLUMN_DT_FIM + ", " + 
						CustomSQLiteOpenHelper.COLUMN_DT_NOT + ", " + 
						CustomSQLiteOpenHelper.COLUMN_TM_NOT + ", " + 
						CustomSQLiteOpenHelper.COLUMN_TIPO  + 
					" from agenda where '" + data + "' between dtIni and dtFim", null);


			ArrayList<Agenda> agendas = new ArrayList<Agenda>();
	        
	        if (cursor.moveToFirst()) {
	            do {
	            	Agenda agenda = new Agenda();
	            	agenda.setId(cursor.getLong(0));
	    			agenda.setTM(cursor.getString(1));
	    			agenda.setMedic(cursor.getString(2));
	    			agenda.setDTIni(cursor.getString(3));
	    			agenda.setDTFim(cursor.getString(4));
	    			agenda.setDTNot(cursor.getString(5));
	    			agenda.setTMNot(cursor.getString(6));
	    			agenda.setTipo(cursor.getString(7));
	    			
	    			agendas.add(agenda);
	            } while (cursor.moveToNext());
	         }
	         if (cursor != null && !cursor.isClosed()) {
	            cursor.close();
	         }
			

			Calendar c1 = Calendar.getInstance();
			Calendar c2 = Calendar.getInstance();
			for (Agenda a : agendas) {
				c1.setTime(df.parse(a.getDTIni()));
				c2.setTime(df.parse(a.getDTFim()));
				while (!c1.after(c2)) {
					MedicamentoDTO med = new MedicamentoDTO();
					med.setData(c1.getTime());
					med.setHorario(a.getTM());
					med.setNomeMedicamento(a.getMedic());
					if((df.format(med.getData()) + med.getHorario()).compareTo(dfDateTime.format(new Date())) != -1){
						retorno.add(med);
					}
					c1.add(Calendar.DAY_OF_MONTH, 1);
				}
			}

			Collections.sort(retorno);
		} catch (Exception e) {
			// TODO: handle exception
		}
         
        return retorno;
	}
	
	public int delete (int id){ 
		return database.delete(CustomSQLiteOpenHelper.TABLE_AGENDA,"id = " + id, null); 
	}
}
